<?php

namespace MLM\Model\Member\Account;

use MLM\Model\Member\Account;

class Storage extends \MLM\Model\Generic\Db\Storage{
    
    public function save(Account $account){
        $insert = $this->_sql->insert()
                ->into('accounts')
                ->values($account->pull() );
        
        return $this->execute($insert);
    }

    public function updateAccount($data, $where){


        $update = $this->_sql->update();
        
        $update->table('accounts');
        
        $update->set($data);
        
        $update->where($where);

        
        
        return $this->execute($update);
    }

    public function getBankList(){
        $sql = "select *
                from bank order by bank_name";

        $stmt = $this->_db->query($sql);
        $res = $stmt->execute();

        return $res;
    }


    public function getAccountBank($memberId) {

       
         $sql = "select x.*, b.bank_name, b.bank_logo from (
                select *
                from accounts
                where member_id = ? 
                ) x left outer join bank b on x.bank_id = b.bank_id";


        $stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($memberId));

        return $res;
    }


    public function getAccountDetail($memberId){
        $sql = "select *
                from accounts
                where member_id = ? ";

        $stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($memberId));

        return $res;
    }


    public function getAccountDetailByAcc($accNum){
        $sql = "select *
                from accounts
                where account_num = ? ";

        $stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($accNum));

        return $res;
    }


   public function getTotalBalance($key) {

        $sql = "select amount, FORMAT(amount,0) as amount2,
                amount_1, FORMAT(amount_1,0) as amount_12
                from accounts
                where account_num = ? ";

        $stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($key));

        return $res;
    }
    
    public function getMemberAccountBank($memberId) {

        $sql = "select bank_acc_no, concat('[',bank_acc_no,']',' ',bank_name,' ','(',account_name,')') as bank_acc_num
                from accounts a, bank b
                where b.bank_id = a.bank_id and a.member_id = ?
                order by bank_acc_no";

        $stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($memberId));

        return $res;
    }

    public function getTotalSaldoByPeriod($memberId, $accNum, $transType, $date1, $date2) {


        $sql = "select FORMAT(sum(amount), 2) as amount
                from accounts ";

        if ($accNum != ""){
            $sql .= " where account_num = '$accNum' ";


        }

        $stmt = $this->_db->query($sql);
        $resultA = $stmt->execute();

        $data = array();
        while ($resultA->next()) {
                $res = $resultA->current();
                $data['saldo'] = $res['amount'];
        }


        $sql = "select FORMAT(sum(amount1),2) as amt, debkred from (
                select trans_dtm, account_num1, FORMAT(amount1,2) as amt_rp, amount1,
                notes_txt as descr,
                mutation_type as debkred
                from v_transaction
                where trans_status = 'COMPLETED'
                and (STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d'))
                and seq = 1 ";

        if ($accNum != ""){
            $sql .= " and (account_num1 = '$accNum' or account_num2 = '$accNum' )";
        }
        
        if ($transType != ""){
            $sql .= " and trans_type_code = '$transType' ";
        }

        $sql .= "  
                ) ww group by debkred";

   

        $stmt = $this->_db->query($sql);
        $resultB = $stmt->execute(array($accNum));

        $data['debet'] = 0;
        $data['kredit'] = 0;

        while ($resultB->next()) {
                $res = $resultB->current();
                
                if ($res['debkred'] == 'K'){
                    $data['kredit'] = $res['amt'];
                }else{
                    $data['debet'] = $res['amt'];
                }
        }

        return $data;

    }


    public function getTotalSaldoMember() {


        $sql = "select sum(amount) as amount
                from accounts ";
        

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $data = array();
        while ($result->next()) {
                $res = $result->current();
                $data['saldo'] = $res['amount'];
        }

       return $data;

    }


    public function getPersonalAcc($memberId, $where){


        $sql = "select *
                from personal_data_trans
                where member_id = ? and ".$where." order by trans_name";

        $stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($memberId));

        return $res;
    }


    public function getPersonalAccByTransCode($memberId, $code){

        $transCode = ($code == 'PLN') ? 'PB02' : 'PB01';

        $sql = "select a.*
                from personal_data_trans a, trans_type b
                where member_id = ? and a.trans_type_id = b.trans_type_id and b.trans_type_code = ? 
                order by trans_name";

        $stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($memberId, $transCode));

        return $res;
    }
}
